package sk.isomi.minihr.mssql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import sk.isomi.minihr.mssql.entities.Address;

public abstract class SQLReader {
	protected Connection conSQL = null;

	protected String server = null;
	protected int port;
	protected String instanceName = null;
	protected String user = null;
	protected String password = null;
	protected String database = null;

	public abstract boolean connect();

	public SQLReader(String server, int port, String instanceName,
			String database, String user, String password) {
		this.server = server;
		this.port = port;
		this.instanceName = instanceName;
		this.database = database;
		this.user = user;
		this.password = password;
	}

	public boolean isSQLConnected() {
		boolean answer = false;

		try {
			if (conSQL != null && !conSQL.isClosed()) {
				answer = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return answer;
	}

	public void testSelect_Statement() {
		if (!isSQLConnected()) {
			System.err.println("Not connected to database");
			return;
		}

		try {
			String query = "select * from Address";

			ResultSet sqlRs = conSQL.createStatement().executeQuery(query);

			while (sqlRs.next()) {
				System.out.println("ID    : " + sqlRs.getInt("ID"));
				System.out.println("Street: " + sqlRs.getString("Street"));
				System.out.println("Town  : " + sqlRs.getString("Town"));
				System.out.println("Zip   : " + sqlRs.getString("ZipCode") + "\n");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conSQL != null && !conSQL.isClosed()) {
					conSQL.close();
					conSQL = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void testSelect_PreparedStatement() {
		if (!isSQLConnected()) {
			System.err.println("Not connected to database");
			return;
		}

		try {
			int[] addressIds = new int[] {1, 3, 5};
			
			String query = "select * from Address where ID=?";
			PreparedStatement preparedStatement = conSQL.prepareStatement(query);
			
			for (int addressId : addressIds) {
				preparedStatement.setInt(1, addressId);
				ResultSet sqlRs = preparedStatement.executeQuery();

				while (sqlRs.next()) {
					System.out.println("ID    : " + sqlRs.getInt("ID"));
					System.out.println("Street: " + sqlRs.getString("Street"));
					System.out.println("Town  : " + sqlRs.getString("Town"));
					System.out.println("Zip   : " + sqlRs.getString("ZipCode") + "\n");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conSQL != null && !conSQL.isClosed()) {
					conSQL.close();
					conSQL = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	
	public void testInsert_PreparedStatement() {
		if (!isSQLConnected()) {
			System.err.println("Not connected to database");
			return;
		}

		try {
			Address[] addresses = new Address[] {
					new Address("Stare Grunty 10", "Bratislava", "84545"),
					new Address("Stare Grunty 11", "Bratislava", "84545"),
					new Address("Stare Grunty 12", "Bratislava", "84545"),
			};
			
			String query = "insert into Address(Street, Town, ZipCode) values(?, ?, ?)";
			PreparedStatement preparedStatement = conSQL.prepareStatement(query);
			
			conSQL.setAutoCommit(false);
			for (Address address : addresses) {
				preparedStatement.setString(1, address.getStreet());
				preparedStatement.setString(2, address.getTown());
				preparedStatement.setString(3, address.getZip());
				int count = preparedStatement.executeUpdate();
				
				
				System.out.println((count != 0 ? "inserted" : "not inserted"));
			}
			conSQL.commit();
		  //conSQL.rollback();
			conSQL.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conSQL != null && !conSQL.isClosed()) {
					conSQL.close();
					conSQL = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void testCall_StoredProcedure() {
		try {
			
			String query = "projectAvgBudget";
			CallableStatement callableStatement = conSQL.prepareCall(query);
			
			ResultSet sqlRs = callableStatement.executeQuery();
			while (sqlRs.next()){
				System.out.println("ProjectAvgBudget: "+sqlRs.getInt(1));
			} // while (sqlRs.next()){
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {		
			try {
				if (conSQL != null && !conSQL.isClosed()){
					conSQL.close();
					conSQL = null;			
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}
